<?php

require_once('tcpdf/config/lang/eng.php');
require_once('tcpdf/tcpdf.php');
include('connect_db.php');

// $act = $_GET[act];
// $date = $_GET[date];

class MYPDF extends TCPDF {

	//Page header
	public function Header() {

		// Title
		$ypage=10;
		$this->SetFont('helvetica', 'B', 12);
		$this->MultiCell('', 5, 'Lembongan Island - Bali', 0, 'L', 0, 0, '', $ypage, true); 
		$this->SetFont('helvetica', '', 10);
		$this->MultiCell('', 5, 'purinusa@hotmail.com +62 366 559 63 72', 0, 'L', 0, 0, '', $ypage+6, true);
		$style3 = array('width' => 0.3, 'cap' => 'round', 'join' => 'round', 'dash' => 0, 'color' => array(0, 0, 0));
		$this->Line(15, $ypage+14, 281, $ypage+14, $style3);
		$this->Line(15, $ypage+15, 281, $ypage+15, $style3);

		// Keterangan laporan
		$this->SetFont('helvetica', 'B', 10);
		$ypage2=28;

		if ($_GET[act] == "monthviewroom") {
			$sub_head = "Month Room Lease Report";
			$exdate = explode("-",$_GET[date]);
			$month_name = array("January", "February", "March", "April", "Mei", "June", "July", "August", "September", "October", "November", "December");
			$month = $exdate[1];
			$display_date = $month_name[$month-1].", ".$exdate[0];
		}elseif ($_GET[act] == "yearviewroom") {
			$sub_head = "Year Room Lease Report";
			$display_date = $_GET[date];
		}
		
		$this->MultiCell('', 5, $sub_head.' in '.$display_date, 0, 'L', 0, 0, '', $ypage2, true);
		$this->SetFont('helvetica', '', 10);
	}

	// Page footer
	public function Footer() {

		$style3 = array('width' => 0.5, 'cap' => 'round', 'join' => 'round', 'dash' => 0, 'color' => array(0, 0, 0));
		$this->Line(10, 285, 200, 285, $style3);
		// Position at 10 mm from bottom
		$this->SetY(-10);
		// Set font
		$this->SetFont('helvetica', 'I', 8);
		// Page number
		$this->Cell(0, 7, 'Page '.$this->getAliasNumPage().' of '.$this->getAliasNbPages(), 0, false, 'C', 0, '', 0, false, 'T', 'M');
		$style = array(
			'position' => '',
			'align' => 'L',
			'stretch' => false,
			'fitwidth' => false,
			'cellfitalign' => '',
			'border' => false,
			'hpadding' => '',
			'vpadding' => '',
			'fgcolor' => array(0,0,0),
			'bgcolor' => false, //array(255,255,255),
			'text' => true,
			'font' => 'helvetica',
			'fontsize' => 6,
			'stretchtext' => 0
		);
	}
}

// create new PDF document
$pdf = new MYPDF('L', 'mm', 'A4', true, 'UTF-8', false);
$pdf->SetCreator(PDF_CREATOR);
$pdf->SetAuthor('Lembongan Island');
$pdf->SetTitle('Report');
$pdf->SetSubject('Room Report');
$pdf->SetKeywords('TCPDF, PDF, example, test, guide');

// set font
$pdf->SetMargins(PDF_MARGIN_LEFT, PDF_MARGIN_TOP, PDF_MARGIN_RIGHT);
$pdf->SetHeaderMargin(PDF_MARGIN_HEADER);
$pdf->SetFooterMargin(PDF_MARGIN_FOOTER);
$pdf->SetAutoPageBreak(false, PDF_MARGIN_BOTTOM);
$pdf->SetFont('helvetica', '', 12);

$pdf->AddPage();

$ypage2=8;

$pdf->SetFont('helvetica', '', 9);

	if ($_GET[act] == "monthviewroom") {

		$html = '
			<table border="1" cellpadding="3">
				<thead>
			    <tr>
			      <td style="vertical-align: middle; text-align: center; width:30px;">No.</td>
			      <td style="vertical-align: middle; text-align: center; width:132px;">Customer Name</td>
			      <td style="vertical-align: middle; text-align: center; width:132px;">Agent Name</td>
			      <td style="vertical-align: middle; text-align: center; width:130px;">Item</td>
			      <td style="vertical-align: middle; text-align: center; width:90px;">Price (IDR)</td>
			      <td style="vertical-align: middle; text-align: center; width:60px;">Guest</td>
			      <td style="vertical-align: middle; text-align: center; width:60px;">Quantity</td>
			      <td style="vertical-align: middle; text-align: center; width:120px;">Amount (IDR)</td>
			    </tr>
			  </thead>

			  <tbody>';
					$grand_total = 0;
	      	$hitungBaris = 0;
					$sql_list_date = "SELECT DATE_FORMAT(a.checkout, '%Y-%c-%d') AS 'date_checkout', 
					DATE_FORMAT(a.checkout, '%M %d, %Y') AS 'date_tampil'
					FROM front_office a 
					WHERE a.is_paid = 1 AND CONCAT(YEAR(a.checkout),'-',MONTH(a.checkout)) = '".$_GET[date]."' 
					GROUP BY DATE_FORMAT(a.checkout, '%Y-%c-%d') ASC";
					$query_list_date = mysql_query($sql_list_date);
					$count_list_date = mysql_num_rows($query_list_date);
					if ($count_list_date > 0) {
						$query_list_date = mysql_query($sql_list_date);
						$i = 1;
						while ($baris_list_date = mysql_fetch_array($query_list_date)) {
							$sql_report = "SELECT a.*, b.nama AS 'room_name' FROM front_office a 
							LEFT JOIN room b on b.id = a.idroom
							WHERE a.is_paid = 1 AND 
							DATE_FORMAT(a.checkout, '%Y-%c-%d') = '".$baris_list_date[date_checkout]."' 
							ORDER BY a.checkout ASC";
							$query_report = mysql_query($sql_report);
							$count_report = mysql_num_rows($query_report);
							if ($count_report > 0) {
								$html=$html.'
								<tr>
									<td colspan="8" style="background-color: #ccc; font-weight:bold;">'.$baris_list_date[date_tampil].'</td>
								</tr>
								';
								$query_report = mysql_query($sql_report);
								while ($baris_report = mysql_fetch_array($query_report)) {
									$sql_service = "SELECT * FROM service_charge a WHERE a.idfront_office = '".$baris_report[id]."'";
									$query_service = mysql_query($sql_service);
									$count_service = mysql_num_rows($query_service);

									$sql_agent = "SELECT * FROM agent WHERE id = '$baris_report[idagent]'";
									$query_agent = mysql_query($sql_agent);
									$result_agent = mysql_fetch_array($query_agent);

									$empty_agent = false;
									if ($result_agent['nama'] == "") {
										$empty_agent = true;
									}

									$diff = abs(strtotime($baris_report[checkout]) - strtotime($baris_report[checkin]));
									$days = floor($diff / (60*60*24));
									$price_kamar = $baris_report[real_paid] * $days * $baris_report[banyak_orang];

									$sub_grand_total = $price_kamar;

									if ($count_service == 0) {
		        				$hitungBaris+=2;
									}else{
		        				$hitungBaris += ($count_service+2);
									}

									$temBaris = $count_service+2;

									if($hitungBaris >= 20){
										$html = $html.'</tbody></table>';
										$pdf->writeHTMLCell(0, 0, '14', $ypage2+28, $html, 0, 1, 0, true, 'L', true);
										$pdf->AddPage();
										$hitungBaris = $temBaris;
										$html = '
										<table border="1" cellpadding="3">
											<thead>
										    <tr>
										      <td style="vertical-align: middle; text-align: center; width:30px;">No.</td>
										      <td style="vertical-align: middle; text-align: center; width:132px;">Customer Name</td>
										      <td style="vertical-align: middle; text-align: center; width:132px;">Agent Name</td>
										      <td style="vertical-align: middle; text-align: center; width:130px;">Item</td>
										      <td style="vertical-align: middle; text-align: center; width:90px;">Price (IDR)</td>
										      <td style="vertical-align: middle; text-align: center; width:60px;">Guest</td>
										      <td style="vertical-align: middle; text-align: center; width:60px;">Quantity</td>
										      <td style="vertical-align: middle; text-align: center; width:120px;">Amount (IDR)</td>
										    </tr>
										  </thead>

										  <tbody>
									  ';
									}

									$html=$html.'
									<tr>
										<td style="vertical-align: middle; text-align: center; width:30px;" rowspan="'.($count_service+2).'">'.$i.'</td>
										<td style="vertical-align: middle; text-align: left; width:132px;" rowspan="'.($count_service+2).'">'.$baris_report[nama].'</td>';
										
									if ($empty_agent) {
										$html=$html.'<td style="vertical-align: middle; text-align: center; width:132px;" rowspan="'.($count_service+2).'">-</td>';
									} else {
										$html=$html.'<td style="vertical-align: middle; text-align: left; width:132px;" rowspan="'.($count_service+2).'">'.$result_agent[nama].'</td>';
									}

									$html=$html.'
										<td style="vertical-align: middle; text-align: left; width:130px;">'.$baris_report[room_name].'</td>
										<td style="vertical-align: middle; text-align: right; width:90px;">'.number_format($baris_report[real_paid],2,",",".").'</td>
										<td style="vertical-align: middle; text-align: center; width:60px;">'.$baris_report[banyak_orang].'</td>
										<td style="vertical-align: middle; text-align: center; width:60px;">'.$days." days".'</td>
										<td style="vertical-align: middle; text-align: right; width:120px;">'.number_format($price_kamar,2,",",".").'</td>
									</tr>
									';
										$query_service = mysql_query($sql_service);
										while ($baris_service = mysql_fetch_array($query_service)) {
											$price_service = $baris_service[harga]*$baris_service[jumlah];
											$html=$html.'
											<tr>
												<td style="vertical-align: middle; text-align: left; width:130px;">'.$baris_service[nama].'</td>
												<td style="vertical-align: middle; text-align: right; width:90px;">'.number_format($baris_service[harga],2,",",".").'</td>
												<td style="vertical-align: middle; text-align: center; width:60px;">-</td>
												<td style="vertical-align: middle; text-align: center; width:60px;">'.$baris_service[jumlah].'</td>
												<td style="vertical-align: middle; text-align: right; width:120px;">'.number_format($price_service,2,",",".").'</td>
											</tr>
											';
											$sub_grand_total = $sub_grand_total+$price_service;
										}
									$html=$html.'
									<tr style="background-color: #eee;">
										<td style="vertical-align: middle; text-align: right;" colspan="4">Amount</td>
										<td style="vertical-align: middle; text-align: right;">'.number_format($sub_grand_total,2,",",".").'</td>
									</tr>
									';
									$grand_total = $grand_total+$sub_grand_total;
									$i++;
								}
							}
						}

						$html=$html.'
						<tr class="grand-total">
							<td style="vertical-align: middle; text-align: right;" colspan="7">Total Amount</td>
							<td style="vertical-align: middle; text-align: right;">'.number_format($grand_total,2,",",".").'</td>
						</tr>
						';
					}

				$html = $html.'
				</tbody>
			</table>
		';
		// end if month act
	}elseif ($_GET[act] == "yearviewroom") {
		
		$html = '
			<table border="1" cellpadding="3">
				<thead>
			    <tr>
			      <td style="vertical-align: middle; text-align: center; width:30px;">No.</td>
			      <td style="vertical-align: middle; text-align: center; width:132px;">Customer Name</td>
			      <td style="vertical-align: middle; text-align: center; width:132px;">Agent Name</td>
			      <td style="vertical-align: middle; text-align: center; width:130px;">Item</td>
			      <td style="vertical-align: middle; text-align: center; width:90px;">Price (IDR)</td>
			      <td style="vertical-align: middle; text-align: center; width:60px;">Guest</td>
			      <td style="vertical-align: middle; text-align: center; width:60px;">Quantity</td>
			      <td style="vertical-align: middle; text-align: center; width:120px;">Amount (IDR)</td>
			    </tr>
			  </thead>

			  <tbody>';
				
				$grand_total = 0;
	      $hitungBaris = 0;
				$sql_list_date = "SELECT DATE_FORMAT(a.checkout, '%Y-%c-%d') AS 'date_checkout', 
				DATE_FORMAT(a.checkout, '%M %d, %Y') AS 'date_tampil'
				FROM front_office a 
				WHERE a.is_paid = 1 AND CONCAT(YEAR(a.checkout)) = '".$_GET[date]."' 
				GROUP BY DATE_FORMAT(a.checkout, '%Y-%c-%d') ASC";
				$query_list_date = mysql_query($sql_list_date);
				$count_list_date = mysql_num_rows($query_list_date);
				if ($count_list_date > 0) {
					$query_list_date = mysql_query($sql_list_date);
					$i = 1;
					while ($baris_list_date = mysql_fetch_array($query_list_date)) {
						$sql_report = "SELECT a.*, b.nama AS nama_room FROM front_office a 
						LEFT JOIN room b ON a.idroom = b.id
						WHERE a.is_paid = 1 AND 
						DATE_FORMAT(a.checkout, '%Y-%c-%d') = '".$baris_list_date[date_checkout]."' 
						ORDER BY a.checkout ASC";
						$query_report = mysql_query($sql_report);
						$count_report = mysql_num_rows($query_report);
						if ($count_report > 0) {
							$html=$html.'
							<tr>
								<td colspan="8" style="background-color: #ccc; font-weight:bold;">'.$baris_list_date[date_tampil].'-'.$hitungBaris.'</td>
							</tr>
							';
							$query_report = mysql_query($sql_report);
							while ($baris_report = mysql_fetch_array($query_report)) {
								$sql_service = "SELECT * FROM service_charge a WHERE a.idfront_office = '".$baris_report[id]."'";
								$query_service = mysql_query($sql_service);
								$count_service = mysql_num_rows($query_service);

								$sql_agent = "SELECT * FROM agent WHERE id = '$baris_report[idagent]'";
								$query_agent = mysql_query($sql_agent);
								$result_agent = mysql_fetch_array($query_agent);

								$empty_agent = false;
								if ($result_agent['nama'] == "") {
									$empty_agent = true;
								}

								$diff = abs(strtotime($baris_report[checkout]) - strtotime($baris_report[checkin]));
								$days = floor($diff / (60*60*24));
								$price_kamar = $baris_report[real_paid] * $days * $baris_report[banyak_orang];

								$sub_grand_total = $price_kamar;

								if ($baris_report[real_paid] == '') {
									$real_paid = 0;
								}else{
									$real_paid = $baris_report[real_paid];
								}

								if ($count_service == 0) {
	        				$hitungBaris+=2;
								}else{
	        				$hitungBaris += ($count_service+2);
								}

								$temBaris = $count_service+2;

								if($hitungBaris >= 20){
									$html = $html.'</tbody></table>';
									$pdf->writeHTMLCell(0, 0, '14', $ypage2+28, $html, 0, 1, 0, true, 'L', true);
									$pdf->AddPage();
									$hitungBaris = $temBaris;
									$html = '
									<table border="1" cellpadding="3">
										<thead>
									    <tr>
									      <td style="vertical-align: middle; text-align: center; width:30px;">No.</td>
									      <td style="vertical-align: middle; text-align: center; width:132px;">Customer Name</td>
									      <td style="vertical-align: middle; text-align: center; width:132px;">Agent Name</td>
									      <td style="vertical-align: middle; text-align: center; width:130px;">Item</td>
									      <td style="vertical-align: middle; text-align: center; width:90px;">Price (IDR)</td>
									      <td style="vertical-align: middle; text-align: center; width:60px;">Guest</td>
									      <td style="vertical-align: middle; text-align: center; width:60px;">Quantity</td>
									      <td style="vertical-align: middle; text-align: center; width:120px;">Amount (IDR)</td>
									    </tr>
									  </thead>

									  <tbody>
								  ';
								}

								$html=$html.'
								<tr>
									<td style="vertical-align: middle; text-align: center; width:30px;" rowspan="'.($count_service+2).'">'.$i.'</td>
									<td style="vertical-align: middle; text-align: left; width:132px;" rowspan="'.($count_service+2).'">'.$baris_report[nama].'</td>';
									
								if ($empty_agent) {
									$html=$html.'<td style="vertical-align: middle; text-align: center; width:132px;" rowspan="'.($count_service+2).'">-</td>';
								} else {
									$html=$html.'<td style="vertical-align: middle; text-align: left; width:132px;" rowspan="'.($count_service+2).'">'.$result_agent[nama].'</td>';
								}

								$html=$html.'
									<td style="vertical-align: middle; text-align: left; width:130px;">'.$baris_report[nama_room].'</td>
									<td style="vertical-align: middle; text-align: right; width:90px;">'.number_format($real_paid,2,",",".").'</td>
									<td style="vertical-align: middle; text-align: center; width:60px;">'.$baris_report[banyak_orang].'</td>
									<td style="vertical-align: middle; text-align: center; width:60px;">'.$days." days".'</td>
									<td style="vertical-align: middle; text-align: right; width:120px;">'.number_format($price_kamar,2,",",".").'</td>
								</tr>
								';
									$query_service = mysql_query($sql_service);
									while ($baris_service = mysql_fetch_array($query_service)) {
										
										if ($baris_service[harga] == '') {
											$harga_service = 0;
											$price_service = 0;
										}else{
											$harga_service = $baris_service[harga];
											$price_service = $baris_service[harga]*$baris_service[jumlah];
										}
										

										$html=$html.'
										<tr>
											<td style="vertical-align: middle; text-align: left; width:130px;">'.$baris_service[nama].'</td>
											<td style="vertical-align: middle; text-align: right; width:90px;">'.number_format($harga_service,2,",",".").'</td>
											<td style="vertical-align: middle; text-align: center; width:60px;">-</td>
											<td style="vertical-align: middle; text-align: center; width:60px;">'.$baris_service[jumlah].'</td>
											<td style="vertical-align: middle; text-align: right; width:120px;">'.number_format($price_service,2,",",".").'</td>
										</tr>
										';
										$sub_grand_total = $sub_grand_total+$price_service;
									}
								$html=$html.'
								<tr style="background-color: #eee;">
									<td style="vertical-align: middle; text-align: right;" colspan="4">Amount</td>
									<td style="vertical-align: middle; text-align: right; width:120px;">'.number_format($sub_grand_total,2,",",".").'</td>
								</tr>
								';
								$grand_total = $grand_total+$sub_grand_total;
								$i++;
							}
						}
					}

					$html=$html.'
					<tr>
						<td style="vertical-align: middle; text-align: right;" colspan="7">Total Amount</td>
						<td style="vertical-align: middle; text-align: right; width:120px;">'.number_format($grand_total,2,",",".").'</td>
					</tr>
					';
				}

				$html = $html.'
				</tbody>
			</table>
		';
		//---end if year act
	} 

$pdf->writeHTMLCell(0, 0, '14', $ypage2+28, $html, 0, 1, 0, true, 'L', true);

//Close and output PDF document
$pdf->Output('Lembongan-Island-'.$_GET[act].'_'.$_GET[date].'.pdf', 'I');

?>